-- 创建数据库（如果不存在）
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'db_enger') THEN
        PERFORM dblink_exec('dbname=' || current_database(), 'CREATE DATABASE db_enger');
    END IF;
END
$$;

\c db_enger;

-- 检查表是否存在
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 't_letter') THEN
        RAISE EXCEPTION 'Tables already exist, exiting...';
    END IF;
END
$$;

-- 创建时间戳函数
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.f_modify_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 1. 字母表
CREATE TABLE t_letter (
    f_id BIGSERIAL PRIMARY KEY,
    f_name VARCHAR(10) NOT NULL,
    f_upper_case CHAR(1) NOT NULL,
    f_lower_case CHAR(1) NOT NULL,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_letter IS '字母表';
COMMENT ON COLUMN t_letter.f_id IS '主键ID';
COMMENT ON COLUMN t_letter.f_name IS '字母名称';
COMMENT ON COLUMN t_letter.f_upper_case IS '大写字母';
COMMENT ON COLUMN t_letter.f_lower_case IS '小写字母';
COMMENT ON COLUMN t_letter.f_create_time IS '创建时间';
COMMENT ON COLUMN t_letter.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_letter_modtime 
    BEFORE UPDATE ON t_letter 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 2. 音标表
CREATE TABLE t_phonetic_symbol (
    f_id BIGSERIAL PRIMARY KEY,
    f_symbol VARCHAR(10) NOT NULL,
    f_description TEXT,
    f_audio_url VARCHAR(255),
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_phonetic_symbol IS '音标表';
COMMENT ON COLUMN t_phonetic_symbol.f_id IS '主键ID';
COMMENT ON COLUMN t_phonetic_symbol.f_symbol IS '音标符号';
COMMENT ON COLUMN t_phonetic_symbol.f_description IS '发音描述';
COMMENT ON COLUMN t_phonetic_symbol.f_audio_url IS '发音音频URL';
COMMENT ON COLUMN t_phonetic_symbol.f_create_time IS '创建时间';
COMMENT ON COLUMN t_phonetic_symbol.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_phonetic_symbol_modtime 
    BEFORE UPDATE ON t_phonetic_symbol 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 3. 单词表
CREATE TABLE t_word (
    f_id BIGSERIAL PRIMARY KEY,
    f_word VARCHAR(100) NOT NULL,
    f_pronunciation VARCHAR(100),
    f_meaning TEXT NOT NULL,
    f_example TEXT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_word ON t_word(f_word);

COMMENT ON TABLE t_word IS '单词表';
COMMENT ON COLUMN t_word.f_id IS '主键ID';
COMMENT ON COLUMN t_word.f_word IS '单词';
COMMENT ON COLUMN t_word.f_pronunciation IS '音标';
COMMENT ON COLUMN t_word.f_meaning IS '词义';
COMMENT ON COLUMN t_word.f_example IS '例句';
COMMENT ON COLUMN t_word.f_create_time IS '创建时间';
COMMENT ON COLUMN t_word.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_word_modtime 
    BEFORE UPDATE ON t_word 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 4. 短语表
CREATE TABLE t_phase (
    f_id BIGSERIAL PRIMARY KEY,
    f_content TEXT NOT NULL,
    f_meaning TEXT NOT NULL,
    f_example TEXT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_phase IS '短语表';
COMMENT ON COLUMN t_phase.f_id IS '主键ID';
COMMENT ON COLUMN t_phase.f_content IS '短语内容';
COMMENT ON COLUMN t_phase.f_meaning IS '含义';
COMMENT ON COLUMN t_phase.f_example IS '例句';
COMMENT ON COLUMN t_phase.f_create_time IS '创建时间';
COMMENT ON COLUMN t_phase.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_phase_modtime 
    BEFORE UPDATE ON t_phase 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 5. 句子表
CREATE TABLE t_sentence (
    f_id BIGSERIAL PRIMARY KEY,
    f_content TEXT NOT NULL,
    f_translation TEXT NOT NULL,
    f_type VARCHAR(50),
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_sentence IS '句子表';
COMMENT ON COLUMN t_sentence.f_id IS '主键ID';
COMMENT ON COLUMN t_sentence.f_content IS '句子内容';
COMMENT ON COLUMN t_sentence.f_translation IS '翻译';
COMMENT ON COLUMN t_sentence.f_type IS '句子类型';
COMMENT ON COLUMN t_sentence.f_create_time IS '创建时间';
COMMENT ON COLUMN t_sentence.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_sentence_modtime 
    BEFORE UPDATE ON t_sentence 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 6. 文章表
CREATE TABLE t_article (
    f_id BIGSERIAL PRIMARY KEY,
    f_title VARCHAR(255) NOT NULL,
    f_content TEXT NOT NULL,
    f_difficulty INT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_article IS '文章表';
COMMENT ON COLUMN t_article.f_id IS '主键ID';
COMMENT ON COLUMN t_article.f_title IS '标题';
COMMENT ON COLUMN t_article.f_content IS '内容';
COMMENT ON COLUMN t_article.f_difficulty IS '难度等级';
COMMENT ON COLUMN t_article.f_create_time IS '创建时间';
COMMENT ON COLUMN t_article.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_article_modtime 
    BEFORE UPDATE ON t_article 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 7. 分类表
CREATE TABLE t_category (
    f_id BIGSERIAL PRIMARY KEY,
    f_name VARCHAR(100) NOT NULL,
    f_parent_id BIGINT,
    f_level INT NOT NULL,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_category IS '分类表';
COMMENT ON COLUMN t_category.f_id IS '主键ID';
COMMENT ON COLUMN t_category.f_name IS '分类名称';
COMMENT ON COLUMN t_category.f_parent_id IS '父分类ID';
COMMENT ON COLUMN t_category.f_level IS '层级';
COMMENT ON COLUMN t_category.f_create_time IS '创建时间';
COMMENT ON COLUMN t_category.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_category_modtime 
    BEFORE UPDATE ON t_category 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 8. 评测模板表
CREATE TABLE t_evaluation_template (
    f_id BIGSERIAL PRIMARY KEY,
    f_name VARCHAR(100) NOT NULL,
    f_rules TEXT NOT NULL,
    f_time_limit INT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_evaluation_template IS '评测模板表';
COMMENT ON COLUMN t_evaluation_template.f_id IS '主键ID';
COMMENT ON COLUMN t_evaluation_template.f_name IS '模板名称';
COMMENT ON COLUMN t_evaluation_template.f_rules IS '评测规则';
COMMENT ON COLUMN t_evaluation_template.f_time_limit IS '时间限制(秒)';
COMMENT ON COLUMN t_evaluation_template.f_create_time IS '创建时间';
COMMENT ON COLUMN t_evaluation_template.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_evaluation_template_modtime 
    BEFORE UPDATE ON t_evaluation_template 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 添加分类表的自引用外键
ALTER TABLE t_category 
    ADD CONSTRAINT fk_category_parent FOREIGN KEY (f_parent_id) REFERENCES t_category(f_id);

-- 最后添加外键约束
ALTER TABLE t_evaluation 
    ADD CONSTRAINT fk_evaluation_template FOREIGN KEY (f_template_id) REFERENCES t_evaluation_template(f_id),
    ADD CONSTRAINT fk_evaluation_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id);

ALTER TABLE t_plan 
    ADD CONSTRAINT fk_plan_template FOREIGN KEY (f_template_id) REFERENCES t_plan_template(f_id),
    ADD CONSTRAINT fk_plan_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id);

ALTER TABLE t_task 
    ADD CONSTRAINT fk_task_template FOREIGN KEY (f_template_id) REFERENCES t_task_template(f_id),
    ADD CONSTRAINT fk_task_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id);

ALTER TABLE t_user_category 
    ADD CONSTRAINT fk_user_category_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id),
    ADD CONSTRAINT fk_user_category_parent FOREIGN KEY (f_parent_id) REFERENCES t_user_category(f_id);

ALTER TABLE t_account 
    ADD CONSTRAINT fk_account_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id);

ALTER TABLE t_account_detail 
    ADD CONSTRAINT fk_account_detail_account FOREIGN KEY (f_account_id) REFERENCES t_account(f_id);

ALTER TABLE t_order 
    ADD CONSTRAINT fk_order_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id);

ALTER TABLE t_order_log 
    ADD CONSTRAINT fk_order_log_order FOREIGN KEY (f_order_id) REFERENCES t_order(f_id);

ALTER TABLE t_report 
    ADD CONSTRAINT fk_report_user FOREIGN KEY (f_user_id) REFERENCES t_user(f_id);

-- 9. 计划模板表
CREATE TABLE t_plan_template (
    f_id BIGSERIAL PRIMARY KEY,
    f_name VARCHAR(100) NOT NULL,
    f_content TEXT NOT NULL,
    f_duration INT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_plan_template IS '计划模板表';
COMMENT ON COLUMN t_plan_template.f_id IS '主键ID';
COMMENT ON COLUMN t_plan_template.f_name IS '模板名称';
COMMENT ON COLUMN t_plan_template.f_content IS '计划内容';
COMMENT ON COLUMN t_plan_template.f_duration IS '计划时长(天)';
COMMENT ON COLUMN t_plan_template.f_create_time IS '创建时间';
COMMENT ON COLUMN t_plan_template.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_plan_template_modtime 
    BEFORE UPDATE ON t_plan_template 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 10. 任务模板表
CREATE TABLE t_task_template (
    f_id BIGSERIAL PRIMARY KEY,
    f_name VARCHAR(100) NOT NULL,
    f_type VARCHAR(50) NOT NULL,
    f_description TEXT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_task_template IS '任务模板表';
COMMENT ON COLUMN t_task_template.f_id IS '主键ID';
COMMENT ON COLUMN t_task_template.f_name IS '模板名称';
COMMENT ON COLUMN t_task_template.f_type IS '任务类型';
COMMENT ON COLUMN t_task_template.f_description IS '任务描述';
COMMENT ON COLUMN t_task_template.f_create_time IS '创建时间';
COMMENT ON COLUMN t_task_template.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_task_template_modtime 
    BEFORE UPDATE ON t_task_template 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 11.1 评测表
CREATE TABLE t_evaluation (
    f_id BIGSERIAL PRIMARY KEY,
    f_template_id BIGINT NOT NULL,
    f_user_id BIGINT NOT NULL,
    f_status VARCHAR(20) NOT NULL,
    f_score DECIMAL(5,2),
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_evaluation IS '评测表';
COMMENT ON COLUMN t_evaluation.f_id IS '主键ID';
COMMENT ON COLUMN t_evaluation.f_template_id IS '模板ID';
COMMENT ON COLUMN t_evaluation.f_user_id IS '用户ID';
COMMENT ON COLUMN t_evaluation.f_status IS '状态';
COMMENT ON COLUMN t_evaluation.f_score IS '评分';
COMMENT ON COLUMN t_evaluation.f_create_time IS '创建时间';
COMMENT ON COLUMN t_evaluation.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_evaluation_modtime 
    BEFORE UPDATE ON t_evaluation 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 11.2 计划表
CREATE TABLE t_plan (
    f_id BIGSERIAL PRIMARY KEY,
    f_template_id BIGINT NOT NULL,
    f_user_id BIGINT NOT NULL,
    f_status VARCHAR(20) NOT NULL,
    f_progress INT DEFAULT 0,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_plan IS '计划表';
COMMENT ON COLUMN t_plan.f_id IS '主键ID';
COMMENT ON COLUMN t_plan.f_template_id IS '模板ID';
COMMENT ON COLUMN t_plan.f_user_id IS '用户ID';
COMMENT ON COLUMN t_plan.f_status IS '状态';
COMMENT ON COLUMN t_plan.f_progress IS '进度';
COMMENT ON COLUMN t_plan.f_create_time IS '创建时间';
COMMENT ON COLUMN t_plan.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_plan_modtime 
    BEFORE UPDATE ON t_plan 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 11.3 任务表
CREATE TABLE t_task (
    f_id BIGSERIAL PRIMARY KEY,
    f_template_id BIGINT NOT NULL,
    f_user_id BIGINT NOT NULL,
    f_status VARCHAR(20) NOT NULL,
    f_result TEXT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_task IS '任务表';
COMMENT ON COLUMN t_task.f_id IS '主键ID';
COMMENT ON COLUMN t_task.f_template_id IS '模板ID';
COMMENT ON COLUMN t_task.f_user_id IS '用户ID';
COMMENT ON COLUMN t_task.f_status IS '状态';
COMMENT ON COLUMN t_task.f_result IS '任务结果';
COMMENT ON COLUMN t_task.f_create_time IS '创建时间';
COMMENT ON COLUMN t_task.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_task_modtime 
    BEFORE UPDATE ON t_task 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 12. 用户自定义分类表
CREATE TABLE t_user_category (
    f_id BIGSERIAL PRIMARY KEY,
    f_user_id BIGINT NOT NULL,
    f_name VARCHAR(100) NOT NULL,
    f_parent_id BIGINT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_user_category IS '用户自定义分类表';
COMMENT ON COLUMN t_user_category.f_id IS '主键ID';
COMMENT ON COLUMN t_user_category.f_user_id IS '用户ID';
COMMENT ON COLUMN t_user_category.f_name IS '分类名称';
COMMENT ON COLUMN t_user_category.f_parent_id IS '父分类ID';
COMMENT ON COLUMN t_user_category.f_create_time IS '创建时间';
COMMENT ON COLUMN t_user_category.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_user_category_modtime 
    BEFORE UPDATE ON t_user_category 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 13. 知识库表
CREATE TABLE t_know_everything (
    f_id BIGSERIAL PRIMARY KEY,
    f_entity_type VARCHAR(50) NOT NULL,
    f_entity_id BIGINT NOT NULL,
    f_key VARCHAR(100) NOT NULL,
    f_value TEXT NOT NULL,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_know_everything IS '知识库表';
COMMENT ON COLUMN t_know_everything.f_id IS '主键ID';
COMMENT ON COLUMN t_know_everything.f_entity_type IS '实体类型';
COMMENT ON COLUMN t_know_everything.f_entity_id IS '实体ID';
COMMENT ON COLUMN t_know_everything.f_key IS '键';
COMMENT ON COLUMN t_know_everything.f_value IS '值';
COMMENT ON COLUMN t_know_everything.f_create_time IS '创建时间';
COMMENT ON COLUMN t_know_everything.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_know_everything_modtime 
    BEFORE UPDATE ON t_know_everything 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 14. 用户表
CREATE TABLE t_user (
    f_id BIGSERIAL PRIMARY KEY,
    f_username VARCHAR(50) NOT NULL,
    f_password VARCHAR(255) NOT NULL,
    f_email VARCHAR(100),
    f_phone VARCHAR(20),
    f_status VARCHAR(20) NOT NULL DEFAULT 'active',
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_user IS '用户表';
COMMENT ON COLUMN t_user.f_id IS '主键ID';
COMMENT ON COLUMN t_user.f_username IS '用户名';
COMMENT ON COLUMN t_user.f_password IS '密码';
COMMENT ON COLUMN t_user.f_email IS '邮箱';
COMMENT ON COLUMN t_user.f_phone IS '手机号';
COMMENT ON COLUMN t_user.f_status IS '状态';
COMMENT ON COLUMN t_user.f_create_time IS '创建时间';
COMMENT ON COLUMN t_user.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_user_modtime 
    BEFORE UPDATE ON t_user 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 创建用户表索引
CREATE UNIQUE INDEX uk_username ON t_user(f_username);
CREATE INDEX idx_email ON t_user(f_email);
CREATE INDEX idx_phone ON t_user(f_phone);

-- 15. 报告表
CREATE TABLE t_report (
    f_id BIGSERIAL PRIMARY KEY,
    f_user_id BIGINT NOT NULL,
    f_type VARCHAR(20) NOT NULL,
    f_period VARCHAR(20) NOT NULL,
    f_content TEXT NOT NULL,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_report IS '报告表';
COMMENT ON COLUMN t_report.f_id IS '主键ID';
COMMENT ON COLUMN t_report.f_user_id IS '用户ID';
COMMENT ON COLUMN t_report.f_type IS '报告类型';
COMMENT ON COLUMN t_report.f_period IS '统计周期';
COMMENT ON COLUMN t_report.f_content IS '报告内容';
COMMENT ON COLUMN t_report.f_create_time IS '创建时间';
COMMENT ON COLUMN t_report.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_report_modtime 
    BEFORE UPDATE ON t_report 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 创建知识库表索引
CREATE INDEX idx_entity ON t_know_everything(f_entity_type, f_entity_id);

-- 16.1 账户表
CREATE TABLE t_account (
    f_id BIGSERIAL PRIMARY KEY,
    f_user_id BIGINT NOT NULL,
    f_balance DECIMAL(10,2) NOT NULL DEFAULT 0,
    f_status VARCHAR(20) NOT NULL DEFAULT 'active',
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_account IS '账户表';
COMMENT ON COLUMN t_account.f_id IS '主键ID';
COMMENT ON COLUMN t_account.f_user_id IS '用户ID';
COMMENT ON COLUMN t_account.f_balance IS '账户余额';
COMMENT ON COLUMN t_account.f_status IS '状态';
COMMENT ON COLUMN t_account.f_create_time IS '创建时间';
COMMENT ON COLUMN t_account.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_account_modtime 
    BEFORE UPDATE ON t_account 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 16.2 账户明细表
CREATE TABLE t_account_detail (
    f_id BIGSERIAL PRIMARY KEY,
    f_account_id BIGINT NOT NULL,
    f_type VARCHAR(20) NOT NULL,
    f_amount DECIMAL(10,2) NOT NULL,
    f_balance DECIMAL(10,2) NOT NULL,
    f_description TEXT,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_account_detail IS '账户明细表';
COMMENT ON COLUMN t_account_detail.f_id IS '主键ID';
COMMENT ON COLUMN t_account_detail.f_account_id IS '账户ID';
COMMENT ON COLUMN t_account_detail.f_type IS '交易类型';
COMMENT ON COLUMN t_account_detail.f_amount IS '交易金额';
COMMENT ON COLUMN t_account_detail.f_balance IS '交易后余额';
COMMENT ON COLUMN t_account_detail.f_description IS '交易描述';
COMMENT ON COLUMN t_account_detail.f_create_time IS '创建时间';
COMMENT ON COLUMN t_account_detail.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_account_detail_modtime 
    BEFORE UPDATE ON t_account_detail 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 17.1 订单表
CREATE TABLE t_order (
    f_id BIGSERIAL PRIMARY KEY,
    f_user_id BIGINT NOT NULL,
    f_order_no VARCHAR(50) NOT NULL,
    f_amount DECIMAL(10,2) NOT NULL,
    f_status VARCHAR(20) NOT NULL,
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_order IS '订单表';
COMMENT ON COLUMN t_order.f_id IS '主键ID';
COMMENT ON COLUMN t_order.f_user_id IS '用户ID';
COMMENT ON COLUMN t_order.f_order_no IS '订单编号';
COMMENT ON COLUMN t_order.f_amount IS '订单金额';
COMMENT ON COLUMN t_order.f_status IS '订单状态';
COMMENT ON COLUMN t_order.f_create_time IS '创建时间';
COMMENT ON COLUMN t_order.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_order_modtime 
    BEFORE UPDATE ON t_order 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column();

-- 创建订单表索引
CREATE UNIQUE INDEX uk_order_no ON t_order(f_order_no);

-- 17.2 订单日志表
CREATE TABLE t_order_log (
    f_id BIGSERIAL PRIMARY KEY,
    f_order_id BIGINT NOT NULL,
    f_status VARCHAR(20) NOT NULL,
    f_remark TEXT,
    f_operator VARCHAR(50),
    f_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    f_modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE t_order_log IS '订单日志表';
COMMENT ON COLUMN t_order_log.f_id IS '主键ID';
COMMENT ON COLUMN t_order_log.f_order_id IS '订单ID';
COMMENT ON COLUMN t_order_log.f_status IS '订单状态';
COMMENT ON COLUMN t_order_log.f_remark IS '备注';
COMMENT ON COLUMN t_order_log.f_operator IS '操作人';
COMMENT ON COLUMN t_order_log.f_create_time IS '创建时间';
COMMENT ON COLUMN t_order_log.f_modify_time IS '修改时间';

CREATE TRIGGER update_t_order_log_modtime 
    BEFORE UPDATE ON t_order_log 
    FOR EACH ROW 
    EXECUTE FUNCTION update_modified_column(); 